using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace egypt.Logic.Data
{
    public class DataAdapter
    {
        SqlConnection conn = null;
        public DataAdapter(string connstring)
        {
            if (conn == null)
                conn = new SqlConnection(connstring);
        }

        public bool AddNews(News n)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_insertNews", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50).Value = n.Title;
                cmd.Parameters.Add("@Content", SqlDbType.Text).Value = n.Content;
                cmd.Parameters.Add("@category", SqlDbType.Int).Value = n.Category;
                cmd.Parameters.Add("@countryID", SqlDbType.Int).Value = n.CountryID;
                cmd.Parameters.Add("@fuwuID", SqlDbType.Int).Value = n.FuwuID;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            finally {
                conn.Close();
            }
            return retVal;
        }

        public bool UpdateNews(News n)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_updateNews", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@newsID", SqlDbType.Int).Value = n.ID;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 50).Value = n.Title;
                cmd.Parameters.Add("@Content", SqlDbType.Text).Value = n.Content;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            return retVal;
        }

        public bool DeleteNews(int newsID)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_deleteNews", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@newsID", SqlDbType.Int).Value = newsID;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            return retVal;
        }

        public News GetNews(int newsID)
        {
            SqlDataReader dr = null;
            News n = null;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_getNews", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@newsID", SqlDbType.Int).Value = newsID;
                conn.Open();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    n = News.GetInstance(Convert.ToInt32(dr["newsID"]), Convert.ToString(dr["title"]), Convert.ToString(dr["content"]), Convert.ToDateTime(dr["AddTime"]), Convert.ToInt32(dr["hit"]), Convert.ToInt32(dr["category"]), Convert.ToInt32(dr["countryID"]), Convert.ToInt32(dr["fuwuID"]));
                }
            }
            catch (Exception)
            { }
            finally {
                if (dr != null)
                    dr.Close();
                dr = null;
                conn.Close();
            }
            return n;
        }

        public List<News> GetNewsList(int categoryID,int pageSize,int pageIndex,out int page)
        {
            List<News> list = new List<News>();
            int tmpPage = 0;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_getNewList", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
                cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                cmd.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
                SqlParameter par_page = new SqlParameter("@page", SqlDbType.Int);
                par_page.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(par_page);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                tmpPage = Convert.ToInt32(par_page.Value);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    list.Add(News.GetInstance(Convert.ToInt32(dr["newsID"]), Convert.ToString(dr["title"]), Convert.ToString(dr["content"]), Convert.ToDateTime(dr["AddTime"]), Convert.ToInt32(dr["hit"]), Convert.ToInt32(dr["category"]),Convert.ToInt32(dr["countryID"]), Convert.ToInt32(dr["fuwuID"])));
                }
            }
            catch (Exception exc)
            { }
            finally{
                page = tmpPage;
                conn.Close();
            }
            return list;
        }

        public List<News> GetNewsList(int categoryID, int pageSize, int pageIndex,int fuwuID,int countryID, out int page)
        {
            List<News> list = new List<News>();
            int tmpPage = 0;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_getNewList", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
                cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                cmd.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
                if (fuwuID > 0)
                    cmd.Parameters.Add("@fuwuID", SqlDbType.Int).Value = fuwuID;
                if (countryID > 0)
                    cmd.Parameters.Add("@countryID", SqlDbType.Int).Value = countryID;

                SqlParameter par_page = new SqlParameter("@page", SqlDbType.Int);
                par_page.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(par_page);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                tmpPage = Convert.ToInt32(par_page.Value);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    list.Add(News.GetInstance(Convert.ToInt32(dr["newsID"]), Convert.ToString(dr["title"]), Convert.ToString(dr["content"]), Convert.ToDateTime(dr["AddTime"]), Convert.ToInt32(dr["hit"]), Convert.ToInt32(dr["category"]), Convert.ToInt32(dr["countryID"]), Convert.ToInt32(dr["fuwuID"])));
                }
            }
            catch (Exception exc)
            { }
            finally
            {
                page = tmpPage;
                conn.Close();
            }
            return list;
        }

        public bool AddCountry(country c)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_insertCountry", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CountryName", SqlDbType.NVarChar, 50).Value = c.Country;
                cmd.Parameters.Add("@flag", SqlDbType.NVarChar, 50).Value = c.Flag;
                cmd.Parameters.Add("@isShow", SqlDbType.Bit).Value = c.IsShow;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            finally
            {
                conn.Close();
            }
            return retVal;
        }

        public List<string> GetCountryName()
        {
            List<string> retVal = new List<string>();
            SqlDataReader dr = null;
            try
            {
                SqlCommand cmd = new SqlCommand("select countryName from n_country", conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    retVal.Add(dr.GetString(0));
                }
            }
            catch (Exception)
            {
                retVal = null;
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                dr = null;
                conn.Close();
            }
            return retVal;
        }
        
        public List<country> GetCountryList()
        {
            List<country> retVal = new List<country>();
            SqlDataReader dr = null;
            try
            {
                SqlCommand cmd = new SqlCommand("select * from n_country", conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    retVal.Add(country.GetInstance(Convert.ToInt32(dr["countryID"]), Convert.ToString(dr["countryName"]), Convert.ToString(dr["flag"]),Convert.ToBoolean(dr["isShow"])));
                }
            }
            catch (Exception)
            {
                retVal = null;
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                dr = null;
                conn.Close();
            }
            return retVal;
        }
        
        public bool UpdateCountry(country c)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_updateCountry", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@countryID", SqlDbType.Int).Value = c.CountryID;
                cmd.Parameters.Add("@countryName", SqlDbType.NVarChar, 50).Value = c.Country;
                cmd.Parameters.Add("@isShow", SqlDbType.Bit).Value = c.IsShow ? 1 : 0;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            return retVal;
        }

        public bool DeleteCountry(int countryID)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_DeleteCountry", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@countryID", SqlDbType.Int).Value = countryID;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            return retVal;
        }

        public List<friendLink> GetFriendLink()
        {
            return null;
        }

        public bool AddFriendLink(friendLink f)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_insertfriendLink", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@linkName", SqlDbType.NVarChar, 50).Value = f.Name;
                cmd.Parameters.Add("@lingUrl", SqlDbType.VarChar,100).Value = f.Url;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            finally
            {
                conn.Close();
            }
            return retVal;
        }
        public bool DeleteFriendLink(int friendLinkId)
        {
            
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("sp_deletefriendLink", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = friendLinkId;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                if (rs == 1)
                {
                    retVal = true;
                }

            }
            catch (Exception)
            { }
            return retVal;
        }
        public bool UpdateFriendLink(friendLink f)
        {
            return true;
        }
        public List<string> GetFuwuName()
        {
            List<string> retVal = new List<string>();
            SqlDataReader dr = null;
            try
            {
                SqlCommand cmd = new SqlCommand("select fuwu from n_fuwu", conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    retVal.Add(dr.GetString(0));
                }
            }
            catch (Exception)
            {
                retVal = null;
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                dr = null;
                conn.Close();
            }
            return retVal;
        }
        
        public List<Fuwu> GetFuwuList()
        {
            List<Fuwu> retVal = new List<Fuwu>();
            SqlDataReader dr = null;
            try
            {
                SqlCommand cmd = new SqlCommand("select * from n_fuwu", conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    retVal.Add(Fuwu.GetInstance(Convert.ToInt32(dr["fuwuID"]), Convert.ToString(dr["fuwu"])));
                }
            }
            catch (Exception)
            {
                retVal = null;
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                dr = null;
                conn.Close();
            }
            return retVal;
        }

        public bool Login(string user, string password)
        {
            bool retVal = false;
            SqlDataReader dr = null;
            try
            {
                SqlCommand cmd = new SqlCommand("select * from n_Admin where [Admin]=@user and [pwd]=@pwd", conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@user", SqlDbType.NVarChar,50).Value = user;
                cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50).Value = password;
                conn.Open();
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = true;
                }
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                dr = null;
                conn.Close();
            }
            return retVal;
        }

        public bool ChangeUserName(string user, string oldPassword, string newUser)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("update n_Admin set [Admin]=@newuser where [Admin]=@user and [pwd]=@pwd", conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@user", SqlDbType.NVarChar, 50).Value = user;
                cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50).Value = oldPassword;
                cmd.Parameters.Add("@newuser", SqlDbType.NVarChar, 50).Value = newUser;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                retVal = (rs == 1);
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
                conn.Close();
            }
            return retVal;
        }

        public bool ChangePassWord(string user, string oldPassword, string newPassword)
        {
            bool retVal = false;
            try
            {
                SqlCommand cmd = new SqlCommand("update n_Admin set [pwd]=@newpwd where [Admin]=@user and [pwd]=@pwd", conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@user", SqlDbType.NVarChar, 50).Value = user;
                cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50).Value = oldPassword;
                cmd.Parameters.Add("@newpwd", SqlDbType.NVarChar, 50).Value = newPassword;
                conn.Open();
                int rs = cmd.ExecuteNonQuery();
                retVal = (rs == 1);
            }
            catch (Exception)
            {
                retVal = false;
            }
            finally
            {
                conn.Close();
            }
            return retVal;
        }




    }
}
